"""
Dinky数据库连接和会话管理
专门用于访问外部Dinky数据库中的dinky_task表
"""

from sqlalchemy import create_engine, text
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.orm import sessionmaker, Session
from typing import AsyncGenerator, Generator
from contextlib import asynccontextmanager, contextmanager

from app.core.config import settings
from app.core.logger import logger

# 创建同步数据库引擎（用于简单查询）
dinky_sync_engine = create_engine(
    settings.DINKY_DATABASE_URL,
    echo=settings.DEBUG,
    pool_pre_ping=True,
    pool_recycle=300,
    pool_size=5,
    max_overflow=10
)

# 创建异步数据库引擎
dinky_async_engine = create_async_engine(
    settings.DINKY_ASYNC_DATABASE_URL,
    echo=settings.DEBUG,
    pool_pre_ping=True,
    pool_recycle=300,
    pool_size=5,
    max_overflow=10
)

# 创建同步会话工厂
DinkySyncSessionLocal = sessionmaker(
    dinky_sync_engine,
    class_=Session,
    expire_on_commit=False
)

# 创建异步会话工厂
DinkyAsyncSessionLocal = async_sessionmaker(
    dinky_async_engine,
    class_=AsyncSession,
    expire_on_commit=False
)


@contextmanager
def get_dinky_sync_db() -> Generator[Session, None, None]:
    """
    获取Dinky数据库同步会话
    用于简单的同步查询
    """
    session = DinkySyncSessionLocal()
    try:
        yield session
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()


async def get_dinky_async_db() -> AsyncGenerator[AsyncSession, None]:
    """
    获取Dinky数据库异步会话
    用于依赖注入
    """
    async with DinkyAsyncSessionLocal() as session:
        try:
            yield session
        except Exception:
            await session.rollback()
            raise
        finally:
            await session.close()


@asynccontextmanager
async def get_dinky_async_session():
    """
    获取Dinky数据库异步会话的上下文管理器
    """
    async with DinkyAsyncSessionLocal() as session:
        try:
            yield session
        except Exception:
            await session.rollback()
            raise
        finally:
            await session.close()


class DinkyDatabaseService:
    """Dinky数据库服务类"""
    
    @staticmethod
    def test_connection() -> dict:
        """测试Dinky数据库连接"""
        try:
            with get_dinky_sync_db() as db:
                result = db.execute(text("SELECT 1")).scalar()
                if result == 1:
                    return {
                        "success": True,
                        "message": "Dinky数据库连接成功"
                    }
                else:
                    return {
                        "success": False,
                        "message": "Dinky数据库连接测试失败"
                    }
        except Exception as e:
            logger.error(f"Dinky数据库连接失败: {str(e)}")
            return {
                "success": False,
                "message": f"Dinky数据库连接失败: {str(e)}"
            }
    
    @staticmethod
    def get_task_statistics() -> dict:
        """获取Dinky任务统计信息"""
        try:
            with get_dinky_sync_db() as db:
                # 总任务数
                total_tasks = db.execute(text("SELECT COUNT(*) FROM dinky_task")).scalar()
                
                # 启用的任务数
                enabled_tasks = db.execute(text("SELECT COUNT(*) FROM dinky_task WHERE enabled = 1")).scalar()
                
                # 有SQL语句的任务数
                tasks_with_sql = db.execute(text(
                    "SELECT COUNT(*) FROM dinky_task WHERE statement IS NOT NULL AND statement != '' AND enabled = 1"
                )).scalar()
                
                return {
                    "success": True,
                    "total_tasks": total_tasks,
                    "enabled_tasks": enabled_tasks,
                    "tasks_with_sql": tasks_with_sql
                }
        except Exception as e:
            logger.error(f"获取Dinky任务统计信息失败: {str(e)}")
            return {
                "success": False,
                "message": f"获取任务统计信息失败: {str(e)}"
            }
    
    @staticmethod
    def get_tasks_for_parsing(limit: int = 1000) -> list:
        """获取用于解析的任务列表"""
        try:
            with get_dinky_sync_db() as db:
                result = db.execute(text("""
                    SELECT id, name, statement
                    FROM dinky_task
                    WHERE statement IS NOT NULL
                    AND statement != ''
                    AND enabled = 1
                    ORDER BY id
                    LIMIT :limit
                """), {"limit": limit})
                
                tasks = result.fetchall()
                return [
                    {
                        "id": task.id,
                        "name": task.name,
                        "statement": task.statement
                    }
                    for task in tasks
                ]
        except Exception as e:
            logger.error(f"获取解析任务列表失败: {str(e)}")
            return []


async def close_dinky_db():
    """关闭Dinky数据库连接"""
    await dinky_async_engine.dispose()
    dinky_sync_engine.dispose()
